package cn.edu.scau.cmi.front.dao;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.edu.scau.cmi.ema.dao.AbstractJpaDao;
import cn.edu.scau.cmi.ema.dao.EventDAO;
import cn.edu.scau.cmi.ema.dao.FoodtypeDAO;
import cn.edu.scau.cmi.ema.domain.Address;
import cn.edu.scau.cmi.ema.domain.Echart;
import cn.edu.scau.cmi.ema.domain.Event;
import cn.edu.scau.cmi.ema.domain.Foodbatch;
import cn.edu.scau.cmi.ema.domain.Foodtype;
import cn.edu.scau.cmi.ema.domain.Picture;
import cn.edu.scau.cmi.ema.domain.base.CmiPagination;
import cn.edu.scau.cmi.ema.service.base.FrontEventSqlService;
import cn.edu.scau.cmi.ema.util.CmiSqlUtil;
import cn.edu.scau.cmi.ema.util.CmiTimeUtil;
import cn.edu.scau.cmi.front.domain.EventFoodbatch;
import cn.edu.scau.cmi.front.domain.FrontEventPagination;

@Repository("CmiEventDAO")
@Transactional
public class CmiEventDAOImpl extends AbstractJpaDao implements CmiEventDAO {

	@PersistenceContext(unitName = "ema")
	private EntityManager entityManager;
	
	@Autowired FrontEventSqlService frontEventSqlService;
	@Autowired EventDAO eventDAO;
	
	@Autowired FoodtypeDAO foodtypeDAO;

	@Override
	public String findAddressNameById(int addressId) {
		Query query = createNamedQuery("findAddressNameById", -1, -1, addressId);
		Object object = query.getSingleResult();
		return (String) object;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public Integer findEventIdByLimit(int startResult, int maxRows) throws DataAccessException {
		Query query = createNamedQuery("findEventIdByLimit", startResult, maxRows);

		return (Integer) query.getSingleResult();
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Event> findEventByLimit(int startResult, int maxRows) throws DataAccessException {
		Query query = createNamedQuery("findEventByLimit", startResult, maxRows);

		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Object> findEventIdAndFoodbatchByLimit(int startResult, int maxRows) throws DataAccessException {
		Query query = createNamedQuery("findEventIdAndFoodbatchByLimit", startResult, maxRows);

		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Object> findEventIdAndFoodbatchByEventNameLimit(String eventName, int startResult, int maxRows)
			throws DataAccessException {
		Query query = createNamedQuery("findEventIdAndFoodbatchByEventNameLimit", startResult, maxRows, eventName);

		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Foodbatch> findFoodbatchsByEventLimit(int startResult, int maxRows, int startId)
			throws DataAccessException {
		Query query = createNamedQuery("findFoodbatchsByEventLimit", startResult, maxRows, startId);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<Integer> findEventIdsByEventLimit(int startResult, int maxRows, int startId)
			throws DataAccessException {
		Query query = createNamedQuery("findEventIdsByEventLimit", startResult, maxRows, startId);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public Set<Event> findEventsByIdDesc(int startResult, int maxRows) throws DataAccessException {
		Query query = createNamedQuery("findEventsByIdDesc", startResult, maxRows);
		return new LinkedHashSet<Event>(query.getResultList());
	}

	@Override
	@Transactional
	public List<Calendar> findEventTimes(Set<Event> events) throws DataAccessException {
		List<Calendar> eventTimes = new ArrayList<Calendar>();
		for (Event event : events) {
			try {
				Query query = createNamedQuery("findEventTime", -1, -1, event.getId());
				eventTimes.add((Calendar) query.getSingleResult());
			} catch (Exception e) {
			}
		}
		return eventTimes;
	}

//	@Override
	public EntityManager getEntityManager() {
		// TODO Auto-generated method stub
		return entityManager;
	}

	@Override
	public boolean canBeMerged(Object o) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public Event executeQueryByNameSingleResult(String queryName) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Event executeQueryByNameSingleResult(String queryName, Object... parameters) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Event executeQuerySingleResult(String queryString, Object... parameters) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Set getTypes() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int findEventsByTime(String hql, int startResult, int maxRows) {
		Query query = createQuery(hql, startResult, maxRows);
		return new Integer((int) query.getSingleResult());
	}

	@Override
	public Set<Event> findEventsWithPictureByFoodtypeIdsDesc(Set<Integer> foodtypeIdSet, int startResult, int maxRows) {
		Query query = createNamedQuery("findEventsWithPictureByFoodtypeIdsDesc", startResult, maxRows, foodtypeIdSet);

		return new HashSet<Event>(query.getResultList());
	}

	@Override
	public Set<Picture> findPicturesWithPictureByEventStatusAndFoodtypeIdsDesc(Set<Boolean> eventStatusSet,
			Set<Integer> foodtypeIdSet, int startResult, int maxRows) {
		Query query = createNamedQuery("findPicturesWithPictureByEventStatusAndFoodtypeIdsDesc", startResult, maxRows,
				eventStatusSet, foodtypeIdSet);

		return new HashSet<Picture>(query.getResultList());
	}

	@Override
	public Set<Event> findAllEventsDesc(int startResult, int maxRows) throws DataAccessException {
		Query query = createNamedQuery("findAllEventsDesc", startResult, maxRows);
		return new LinkedHashSet<Event>(query.getResultList());
	}

	@Override
	public String findMaxDiscoveryTimeInEventByDiscoveryAddress(Set<Integer> addressIdSet, int startResult, int maxRows)
			throws DataAccessException {
		Query query = createNamedQuery("findMaxDiscoveryTimeInEventByDiscoveryAddress", startResult, maxRows,
				addressIdSet);
		return (String) query.getSingleResult();
	}

	@Override
	public String findMinDiscoveryTimeInEventByDiscoveryAddress(Set<Integer> addressIdSet, int startResult, int maxRows)
			throws DataAccessException {
		Query query = createNamedQuery("findMinDiscoveryTimeInEventByDiscoveryAddress", startResult, maxRows,
				addressIdSet);
		return (String) query.getSingleResult();
	}

	@Override
	public Long countEventsByDiscoveryAddressAndfoodtypeIds(Integer addressId, Set<Integer> foodtypeIdSet,
			int startResult, int maxRows) throws DataAccessException {
		Query query = null;
		if (foodtypeIdSet == null || foodtypeIdSet.size() <= 0) {
			query = createNamedQuery("countEventsByDiscoveryAddress", startResult, maxRows, addressId);
		} else {
			query = createNamedQuery("countEventsByDiscoveryAddressAndfoodtypeIds", startResult, maxRows, addressId,
					foodtypeIdSet);
		}
		// TODO Auto-generated method stub
		return (Long) query.getSingleResult();
	}

	@Override
	public Long countEventsByDiscoveryAddressAndDiscoveryTimeAndfoodtypeIds(Integer addressId, String time,
			Set<Integer> foodtypeIdSet, int startResult, int maxRows) throws DataAccessException {
		Query query = null;
		if (foodtypeIdSet == null || foodtypeIdSet.size() <= 0) {
			query = createNamedQuery("countEventsByDiscoveryAddressAndDiscoveryTime", startResult, maxRows, addressId,
					time);
		} else {
			query = createNamedQuery("countEventsByDiscoveryAddressAndDiscoveryTimeAndfoodtypeIds", startResult,
					maxRows, addressId, time, foodtypeIdSet);
		}
		// TODO Auto-generated method stub
		return (Long) query.getSingleResult();
	}

	@Override
	public Set<Event> findEventsByFoodtypeId(Set<Integer> foodtypeIdSet, int startResult, int maxRows)
			throws DataAccessException {
		Query query = createNamedQuery("findEventsByFoodtypeId", startResult, maxRows, foodtypeIdSet);

		return new HashSet<Event>(query.getResultList());
	}

//	(1) 关联事件：主体 -> 食物名称 -> 发现地址 -> 发现时间 -> 比较麻烦，暂时使用食物的名称来匹配，如果不够5个，就用主体，来源地址，继续的话，就使用
	@Override
	public List<Event> findFoodRelativEvents(Integer eventId, int startResult, int maxRows) throws DataAccessException {

		Event event = eventDAO.findEventById(eventId);
		Query query = createNamedQuery("findFoodRelativEvents", startResult, maxRows+1, event.getFoodbatch().getFood().getName());
		List<Event> events = query.getResultList();
//		TODO 从结果中删除本event对象。有可能包空指针异常，暂时不删除。
//		events.remove(event);
		
		return events;
	}
	
//	(2) 关联事件：主体 -> 食物名称 -> 发现地址 -> 发现时间 -> 比较麻烦，暂时使用食物的名称来匹配，如果不够5个，就用主体，来源地址，继续的话，就使用
	@Override
	public List<Event> findFoodtypeRelativEvents(Integer eventId, int startResult, int maxRows) throws DataAccessException {

		Event event = eventDAO.findEventById(eventId);
		Query query = createNamedQuery("findFoodtypeRelativEvents", startResult, maxRows+1, event.getFoodbatch().getFood().getFoodtype());
		List<Event> events = query.getResultList();
//		TODO 从结果中删除本event对象。有可能包空指针异常，暂时不删除。
//		events.remove(event);
		
		return events;
	}
	
//	(3) 关联事件：主体 -> 食物名称 -> 发现地址 -> 发现时间 -> 比较麻烦，暂时使用食物的名称来匹配，如果不够5个，就用主体，来源地址，继续的话，就使用
	@Override
	public List<Event> findDiscoveryadddressRelativEvents(Integer eventId, int startResult, int maxRows) throws DataAccessException {

		Event event = eventDAO.findEventById(eventId);
		Query query = createNamedQuery("findDiscoveryadddressRelativEvents", startResult, maxRows+1, event.getDiscoveryAddress());
		List<Event> events = query.getResultList();
//		TODO 从结果中删除本event对象。有可能包空指针异常，暂时不删除。
//		events.remove(event);
		
		return events;
	}
	
//	(4) 关联事件：主体 -> 食物名称 -> 发现地址 -> 发现时间 -> 比较麻烦，暂时使用食物的名称来匹配，如果不够5个，就用主体，来源地址，继续的话，就使用
	@Override
	public List<Event> findSourceAddressRelativEvents(Integer eventId, int startResult, int maxRows) throws DataAccessException {

		Event event = eventDAO.findEventById(eventId);
		Query query = createNamedQuery("findSourceAddressRelativEvents", startResult, maxRows+1, event.getSourceAddress());
		List<Event> events = query.getResultList();
//		TODO 从结果中删除本event对象。有可能包空指针异常，暂时不删除。
//		events.remove(event);
		
		return events;
	}
	
	
	
	
	
	
	
	
	@Override
	public Integer countEventsByFoodtypeId(Set<Integer> foodtypeIdSet, int startResult, int maxRows)
			throws DataAccessException {
		Query query = createNamedQuery("countEventsByFoodtypeId", startResult, maxRows, foodtypeIdSet);
		// TODO Auto-generated method stub
		return Integer.valueOf(String.valueOf(query.getSingleResult()));
	}

	@Override
	@Transactional
	public Integer countEventsByDetachedCriteriaInRange(FrontEventPagination page) throws DataAccessException {
		/**
		 * 此处会使用到eventIds
		 */
		DetachedCriteria detachedCriteria = frontEventSqlService.createDetachedCriteria(page, Event.class);
		Session session = entityManager.unwrap(Session.class);
		detachedCriteria.setProjection(Projections.rowCount());
		return new Long((long) detachedCriteria.getExecutableCriteria(session).uniqueResult()).intValue();
	}
	
	
	/*
	 * @Override public Integer countEventsByDetachedCriteriaInRange(CmiPagination
	 * page) throws DataAccessException { // TODO Auto-generated method stub return
	 * null; }
	 */
	

	/*
	 * @Transactional public Integer
	 * countEventsByDetachedCriteriaInRange(DetachedCriteria
	 * detachedCriteria,CmiPagination page) throws DataAccessException {
	 *//**
		 * 此处会使用到eventIds
		 *//*
			 * Session session = entityManager.unwrap(org.hibernate.Session.class);
			 * detachedCriteria.setProjection(Projections.rowCount()); return new
			 * Long((long)
			 * detachedCriteria.getExecutableCriteria(session).uniqueResult()).intValue(); }
			 */

	

	
	
	
	@Transactional
	public LinkedHashSet<Event> findEventsByDetachedCriteria(FrontEventPagination page) throws DataAccessException {
//		DetachedCriteria detachedCriteria = CmiDetachedCriteriaUtil.createDetachedCriteria(page, Event.class);
		DetachedCriteria detachedCriteria = frontEventSqlService.createDetachedCriteria(page, Event.class);
		Session session = entityManager.unwrap(Session.class);
		
		List<Event> eventList = detachedCriteria.getExecutableCriteria(session).setFirstResult(page.getSearchBeginCursor()).setMaxResults(page.getPaginationSize()).list();
		LinkedHashSet<Event> events = new LinkedHashSet<Event>(eventList);

		return events;
	}
	

	@Override
	public Integer countEventByAddressName(String addressName, String addressType) {

		Session session = entityManager.unwrap(Session.class);
		String sql = "select count(eventid) from echart where " + addressType + "province = ?"; // 从echart表获取事件的数量
		List counts = session.createSQLQuery(sql).setString(0, addressName).list();
		return Integer.parseInt(counts.get(0).toString());
	}

	@Override
	public Integer countEventByAddressNameAndYear(String addressName, String addressType, Integer year,
			String timeType) {
		if (year == null && timeType == null) {
			return countEventByAddressName(addressName, addressType);
		}
		Session session = entityManager.unwrap(Session.class);
		String sql = "select count(event) from echart where " + addressType + "province = ? and year(" + timeType
				+ "Time) = ?"; // 从echart表获取事件的数量
		List counts = session.createSQLQuery(sql).setString(0, addressName).setInteger(1, year).list();
		return Integer.parseInt(counts.get(0).toString());
	}

	@Override
	public Integer countEventByFoodtypeId(Integer foodtypeId) {

		Session session = entityManager.unwrap(Session.class);
		String sql = "select count(event) from echart where foodtype = ?"; // 从echart表获取事件的数量
		List counts = session.createSQLQuery(sql).setInteger(0, foodtypeId).list();
//		System.out.println(counts);
		return Integer.parseInt(counts.get(0).toString());
	}

	@Override
	public Long countEventByAddressNameAndTime(String addressName, String addressType, String timeOfYear,
			String timeType) {
		Session session = entityManager.unwrap(Session.class);
		String sql = "select count(event) from echart where " + addressType + "province = ? and YEAR(" + timeType
				+ "Time) = ?"; // 从echart表获取事件的数量
		List counts = session.createSQLQuery(sql).setString(0, addressName).setString(1, timeOfYear).list();
//		System.out.println(counts);
		return Long.parseLong(counts.get(0).toString());
	}

	@Override
	public Long countEventByFoodtypeId(Integer foodtypeId, String timeOfYear, String timeType) {
		Session session = entityManager.unwrap(Session.class);
		String sql = "select count(event) from echart where foodtype = ? and YEAR(" + timeType + "Time) = ?"; // 从echart表获取事件的数量
		List counts = session.createSQLQuery(sql).setInteger(0, foodtypeId).setString(1, timeOfYear).list();
		return Long.parseLong(counts.get(0).toString());
	}

	@Override
	public Integer countEventByAddressNameAndMap(String addressName, String addressType, Map<String, String> map) {
		if (map == null || map.isEmpty()) {
			return countEventByAddressName(addressName, addressType);
		}
		Session session = entityManager.unwrap(Session.class);
		List<String> attrNames = new ArrayList<>(map.keySet());
		StringBuilder sql = new StringBuilder(
				"select count(event) from echart where " + addressType + "province = ?");
		int count = 1;
		for (String attrName : attrNames) {
			if (attrName.contains("year") || attrName.contains("time") || attrName.contains("Time")) {
				sql.append(" and year(" + attrName + ") between ? and ?");
				count += 2;
			} else if (attrName.equals("foodtype")) {
				sql.append(" and " + attrName + " = ?");
				count++;
			}
		}
		org.hibernate.Query query = session.createSQLQuery(sql.toString()).setString(0, addressName);
		for (int i = 0, j = 1; i < attrNames.size() && j < count; i++, j++) {
			String attrName = attrNames.get(i);
			String value = map.get(attrName);
			if (attrName.contains("year") || attrName.contains("time") || attrName.contains("Time")) {
				Integer startYear = Integer.valueOf(value.split("-")[0]);
				Integer endYear = Integer.valueOf(value.split("-")[1]);
				query.setInteger(j, Integer.valueOf(startYear));
				j++;
				query.setInteger(j, Integer.valueOf(endYear));
			} else if (attrName.equals("foodtype")) {
				query.setInteger(j, Integer.valueOf(value));
			}
		}
		List counts = query.list();
		return Integer.parseInt(counts.get(0).toString());
	}

	//统计事件的数量，用于echart中的地图与各种表格，使用的是JDBC本地查询，需要替换成Hibernata的方式。
//	查询条件：eventCondtionMap : {addressType=discoveryprovince, foodtypeId=1642, timeType=discoveryTime, starTime=, endTime=}
	@Override
	public Integer countEvent4MapAndBarTmp(Map<String, String> eventConditionMap) {
		//map数据特征：
		//timeType=discoveryTime, starTime=2000-10-10, endTime=2020-10-10, addressType=discoveryprovince, province=上海市, 
		//		foodTypeId=0, 有可能是空，也可能是页面传来的所有的id 0.
		if (eventConditionMap == null || eventConditionMap.isEmpty()) {	return 0;}
		//		如果地址类型为空，表示没有选择地址类型，或者刚从浏览器初始化请求
		
		String addressType = eventConditionMap.get("addressType");
		String addressName = eventConditionMap.get("province");
		String foodTypeId = eventConditionMap.get("foodtypeId");
		String timeType = eventConditionMap.get("timeType");//discoveryTime  sourceTime
		String starTime = eventConditionMap.get("starTime");
		String endTime = eventConditionMap.get("endTime");
		

		
//		食用Criteria查询可能更好写代码
		Session session = entityManager.unwrap(Session.class);
		Criteria criteria = session.createCriteria(Echart.class);
		
		if(addressType.contentEquals("discoveryprovince")) {
			criteria.add(Restrictions.eq("discoveryprovince", addressName));
		}else if(addressType.contentEquals("sourceprovince")){
			criteria.add(Restrictions.eq("sourceprovince", addressName));
		}
		
		if(!foodTypeId.trim().equals("")  && !foodTypeId.equals("0")) {
			criteria.add(Restrictions.eq("foodmaintype", foodtypeDAO.findFoodtypeById(Integer.parseInt(foodTypeId)).getName()));
		}
//		TODO 时间范围还有问题，待后期修复。
//		Calendar beginCalendar = Calendar.getInstance();
//		Calendar nowCalendar = Calendar.getInstance();
//		beginCalendar.set(Calendar.YEAR, 2000);
//		
//		if(timeType.trim().contentEquals("discoveryTime")) {
//			criteria.add(Restrictions.between("discoveryTime", starTime, endTime));
//		}
//		if(timeType.trim().contentEquals("sourceTime")) {
//			criteria.add(Restrictions.between("sourceTime", starTime, endTime));
//		}
		
		List echarts = criteria.list();
		
		return echarts.size();

//		HQL的拼接方式太原始了，采用Criteria的条件查询。
		// （0）sql语句
//		StringBuilder sql = new StringBuilder("select count(event) from echart where");
//		int args = 0;//参数的数量？？？默认有discoveryprovince这个参数！！！！！！
//		
////		（1.2）拼凑sql语句  地点，地点条件不能为空
//		if(addressType != null) {
//			sql.append(" " + addressType + " =?  ");
//			args++;
//		}else {
//			sql.append("  discoveryprovince  =?  ");
//			args++;
//		}
//		// （1.1）拼凑sql语句  时间
//		if(timeType != null && !starTime.equals("") && !endTime.equals("")) {
//			sql.append(" and " + timeType + " between ? and ? ");
//			args+=2;
//		}
//		
////		（1.3）拼凑sql语句  foodtypeid	
//		if((foodTypeId !=null) && !foodTypeId.equals("0")) {//既不为空，也不为0，需要填上食物类型的条件
//			sql.append(" and " + "foodtype = ? ");
//			args++;
//		}
//		
//		// （2）给sql语句赋值，与上面的顺序要一致！！！
//		Session session = entityManager.unwrap(org.hibernate.Session.class);
//		org.hibernate.Query query = session.createSQLQuery(sql.toString());
//		
//		int argPositon = 0;
//		
////		 （2.2）地址参数
//		query.setString(argPositon++, addressName);
//		
//		// （2.1）时间参数，事件类型为空，那么这些时间肯定应该是空的！！！
//		if(starTime !=null && endTime !=null && !starTime.equals("") && !endTime.equals("")) {
//			query.setString(argPositon, starTime);
//			argPositon++;
//			query.setString(argPositon, endTime);
//			argPositon++;
//		}
//		
////		 （2.3）食物类型
//		
//		if((foodTypeId !=null) && !foodTypeId.equals("0")) {
//			query.setString(argPositon, foodTypeId);
//			args++;
//		}
//		
////		select count(event) from echart where mapTimeType between ? and ? and mapStartEndTime = ? and mapFoodtypeId = ? and discoveryprovince = ?
//		List<?> counts = query.list();
//		
		
	}
	
	@Override
	public Integer countEventByMap(Map<String, String> map) {
		
//		mapTimeType=0, mapStarTime=2020-10-01, mapEndTime=2020-10-09, mapFoodtypeId=0, discoveryprovince=上海市
//		mapTimeType=0, mapTime=2020-10-01#2020-10-31, mapFoodtypeId=0, discoveryprovince=上海市
//		字段名，值，如果是时间，就使用between
//		sourcecountry=中国, sourceTime=#, foodTypeId=0, discoveryprovince=上海市}
		
		
		if (map == null || map.isEmpty()) {
			return 0;
		}
		Session session = entityManager.unwrap(Session.class);
		List<String> attrNames = new ArrayList<>(map.keySet());
		StringBuilder sql = new StringBuilder("select count(event) from echart where");
		int count = 0;
		for (String attrName : attrNames) {
			if (count != 0) sql.append(" and ");
//			如果有时间，就包含开始时间和结束时间
			
//			时间类型	
			
			if (attrName.contains("Time")) {
				sql.append(" " + attrName + " between ? and ?");
				count += 2;
			} else {
				sql.append(" " + attrName + " = ?");
				count++;
			}
		}
		org.hibernate.Query query = session.createSQLQuery(sql.toString());
		for (int i = 0, j = 0; i < attrNames.size() && j < count; i++, j++) {
			String attrName = attrNames.get(i);
			String value = map.get(attrName);
//			mysql能自动将2020-10-05模式的字符串转换成mysql的date类型，如果日期格式有错误，结果将是 0000-00-00的没有实际意义的值。
//			MySQL能自动将字符串转换成整型数据，注意：123abc1234会从头开始截取最长的有效数据。
		
			if (attrName.contains("Time")) {
//				有可能还没有选择，此时，时间还没有值，使用从0000-00-00到当前的时间	
				if( value.length()>1) {
					String startTime = value.split("#")[0];
					String endTime = value.split("#")[1];
					query.setString(j, startTime);
					j++;
					query.setString(j, endTime);
				}else {
					String startTime = "0000-00-00";
					String endTime = CmiTimeUtil.getYMDString(Calendar.getInstance());
					query.setString(j, startTime);
					j++;
					query.setString(j, endTime);
				}
				
			} else {
				query.setString(j, value);
			}
		}
//		select count(event) from echart where mapTimeType between ? and ? and mapStartEndTime = ? and mapFoodtypeId = ? and discoveryprovince = ?
		List counts = query.list();
		return Integer.parseInt(counts.get(0).toString());
	}

	@Override
	public Integer countEventByFoodtypeIdAndMap(Integer foodtypeId, Map<String, String> map) {
		if (map == null || map.isEmpty()) {
			return countEventByFoodtypeId(foodtypeId);
		}
		Session session = entityManager.unwrap(Session.class);
		List<String> attrNames = new ArrayList<>(map.keySet());
		StringBuilder sql = new StringBuilder("select count(event) from echart where foodtype = ?");
		int count = 1;
		for (String attrName : attrNames) {
			if (attrName.contains("year") || attrName.contains("time") || attrName.contains("Time")) {
				sql.append(" and year(" + attrName + ") between ? and ?");
				count += 2;
			}
		}
		org.hibernate.Query query = session.createSQLQuery(sql.toString()).setInteger(0, foodtypeId);
		for (int i = 0, j = 1; i < attrNames.size() && j < count; i++, j++) {
			String attrName = attrNames.get(i);
			String value = map.get(attrName);
			if (attrName.contains("year") || attrName.contains("time") || attrName.contains("Time")) {
				Integer startYear = Integer.valueOf(value.split("-")[0]);
				Integer endYear = Integer.valueOf(value.split("-")[1]);
				query.setInteger(j, Integer.valueOf(startYear));
				j++;
				query.setInteger(j, Integer.valueOf(endYear));
			}
		}
		List counts = query.list();
		return Integer.parseInt(counts.get(0).toString());
	}

	@Override
	public Long countEventByAddressNameAndTime(String addressName, String addressType, String timeOfYear,
			String timeType, Map<String, String> map) {
		if (map == null || map.isEmpty()) {
			countEventByAddressNameAndTime(addressName, addressType, timeOfYear, timeType);
		}
		Session session = entityManager.unwrap(Session.class);
		StringBuilder sql = new StringBuilder("select count(event) from echart where " + addressType
				+ "province = ? and YEAR(" + timeType + "Time) = ?"); // 从echart表获取事件的数量
		// 用于记录占位符数量
		int count = 2;
		List<String> attrNames = new ArrayList<>(map.keySet());
		for (String attrName : attrNames) {
			// 在这里写sql拼接逻辑，记得增长count
		}
		org.hibernate.Query query = session.createSQLQuery(sql.toString()).setString(0, addressName).setString(1,
				timeOfYear);
		// j的初始值应和count初始化值一致
		for (int i = 0, j = 2; i < attrNames.size() && j < count; i++, j++) {
			String attrName = attrNames.get(i);
			String value = map.get(attrName);
			// 这里写占位符填充逻辑
		}
		List counts = query.list();
		return Long.parseLong(counts.get(0).toString());
	}

	@Override
	public Long countEventByFoodtypeId(Integer foodtypeId, String timeOfYear, String timeType,
			Map<String, String> map) {
		if (map == null || map.isEmpty()) {
			countEventByFoodtypeId(foodtypeId, timeOfYear, timeType);
		}
		Session session = entityManager.unwrap(Session.class);
		StringBuilder sql = new StringBuilder(
				"select count(event) from echart where foodtype = ? and YEAR(" + timeType + "Time) = ?"); // 从echart表获取事件的数量
		int count = 2;
		List<String> attrNames = new ArrayList<>(map.keySet());
		for (String attrName : attrNames) {
			// 在这里写sql拼接逻辑，记得增长count
		}
		org.hibernate.Query query = session.createSQLQuery(sql.toString()).setInteger(0, foodtypeId).setString(1, timeOfYear);
		// j的初始值应和count初始化值一致
		for (int i = 0, j = 2; i < attrNames.size() && j < count; i++, j++) {
			String attrName = attrNames.get(i);
			String value = map.get(attrName);
			// 这里写占位符填充逻辑
		}
		List counts = query.list();
		return Long.parseLong(counts.get(0).toString());
	}

	@Override
	public Set<Event> findEventByStatusAndDescription(boolean isAduit, String description, int startId, int maxQuantity) {
		Query query = createNamedQuery("findEventByStatusAndDescription", startId, maxQuantity, isAduit, description);

		return new HashSet<Event>(query.getResultList());
	}

	@Override
	public Event findLastEvent() {
		Query query = createNamedQuery("findLastEvent", -1, -1);
		return  (Event) query.getSingleResult();
	}

	
	@Override
	public Long countEcharteventsByDiscoveryaddressAndDiscoverytime(Address discoveryAddress, int year, Foodtype foodtype) {
		Query query = createNamedQuery("countEcharteventsByDiscoveryaddressAndDiscoverytime", -1, -1, discoveryAddress.getName(), year);
		return (Long) query.getSingleResult();
	}
	
	@Override
	public Long countEcharteventsByFoodtypeAndDiscoverytime(Foodtype foodtype, int year) {
		Query query = createNamedQuery("countEcharteventsByFoodtypeAndDiscoverytime", -1, -1, foodtype.getName(), year);
		return (Long) query.getSingleResult();
	}

}